Note: A new version has been talked about for years, but it is not a guarantee. This post is my way of introducing the new features of SQL Server, and follow on posts will be code filled example of how the features will work. I am making no promises at this time, but when I can, I will.

With the recent release of a CTP version of what is called SQL Server 2016, it is time for me to start thinking about what to add to the next edition of the SQL Server Design Book. In 2014, we decided that there really wasn’t enough change to merit a new edition of the book, with just a few key features being added that changed how one might design their database application. One major feature cropped up, that being In Memory OLTP databases, and while it is a game changer, it was very new and we decided to wait. I went to Kalen Delaney’s precon, as well as producing a presentation on the feature, doing my best to get a feel for how it changed database design.

SQL Server 2016, even in the CTP2 version I am basing this blog post on, contains quite a few new features that will change how we may implement databases, including one that is going to be a tremendous change for the better. In this first post, I want to list the features I think are important to address in my book, and ask you to use the comments to tell me if I am wrong and what other features I missed.  As I progress, I will come back and try to include links to the actual posts (to make it easy for me to use this a a reference later!)

New Features To Possibly Cover

This should be the easiest list to create. There are some very particularly interesting features that are coming to 2016 and that were in 2014, with a brief introduction to what they are and how they fit in my vision.  Each topic will be the header of a linked list of blogs as I write new content on each feature.

• In-Memory OLTP – Allows you to house your database in RAM, not just caching the existing structures in the way on-disk tables are, but using completely new plumbing that is lock and latch free. It is a MAJOR difference architecturally, and may turn out to be the most work making all of the terminology fit (what used to be a table will quite often need to be an “on-disk” table.) Existed in 2014, and enhanced in 2016.
• Temporal Data – This is probably the most exiting feature I have seen. It will make the scenario of keeping up with changes in your tables over time, and automate it. Queries using the tables will be able to be executed in the context of a time period (VERY simplified explanation). New to 2016, and very exiting.
• Columnstore Index Improvements – There is a chapter on reporting structures written by Jessica Moss that touched on Columnstore indexes, at the time a new feature that had just begun to be understood. At this point, with four more years of experience, I know I have a great admiration for what they can do. 2014 gave us Clustered Columnstores, and 2016 enhances how they work to make managing them easier.
• Row Level Security – Allows you to limit access to rows based on the details of the query executor. Not DDL-esque, like GRANT EXECUTE TO Rows Like This, but more like a security policy, where you build a function that is bound and executed on access to the table. It works very similar to the more manual (always go through a stored procedure!) methods that have existed in the past. New for 2016.
• JSON Support –  While I have purposefully avoided JSON in the same manner I avoided XML, I will at least need to cover the existence of this feature.
• Stretch Database – Allows you to put some of your data on the Microsoft Azure Cloud storage (or something along these lines). Allows you to put infrequently used data in the cloud, with very active data locally. Probably not going to be covered heavily, but will bear a mention in the physical design sections of the book.
• Security DDL Enhancements – There were a few server level permissions added to 2014 that were the bomb. CONNECT ANY DATABASE, SELECT ALL USER SECURABLES, and IMPERSONATE ANY LOGIN. The first two allow you to say: Login X can access all database and see all data. Something that a support person may be allowed. In the past, the login would have been given sysadmin rights because, you know, it is just too much work. In 2016, a few new security bits are added to support the new features.
• Always Encrypted – Allows for SQL Server columns to be encrypted based on a key that does not reside on the SQL Server.
• Dynamic Data Masking – A feature that makes column level security palatable by defining a masked output that occurs when a user without certain permissions accesses the column (even though they do have select rights).
• Durability Changes – In SQL Server 2014, along with In-Memory OLTP allowed for non-durable tables. But in addition to In-Memory durability, they included a feature at the database level called delayed durability that makes log writes asynchronous.
• Incremental Statistics – Lets you update stats on a single partition of a table, rather than the entire table (assuming it is partitioned). Probably only gets a brief mention, as I don’t dig into partitioning that much, but it is definitely something that might affect your physical design.

New Material To Add To the Book

This section is about concepts that I need to add that are not based on new features, but rather just concepts that I feel like I missed or didn’t cover enough last time.

• Design Testing – Basically making sure that the architect of a system, as they progress through the requirements start thinking about testing before code is created, and apply the tests to the conceptual, logical, and physical models to make sure they meet the requirements. Too often you see designs that just don’t make sense because the entire process wasn’t walked through before the customer has a UI on their web browser.
• Hierarchies – Enhance the sections about hierarchies a bit to include performance testing characteristics of hierarchy code patterns. This is what my presentation on hierarchies is based on, as I had the idea as the last edition went to print.
• General Tips and Tricks – Other bits and pieces of information about SQL Server and T-SQL programming that come into play.

Stuff to Not Cover

Everyone needs limits, and unless I truly get more space in the book, the following topics will probably stay only the briefest of mentions in this edition.

• Hadoop integration with SQL Server – From what I currently understand, this will allow you to access Hadoop data from SQL Server. Probably will end up in the list of features that may not fit the “Relational Database Design Book” paradigm.

Stuff to Remove

For now, this section will be blank. I personally hope to just add new material, and leave most of the existing stuff intact (unless it only pertains to 2005 or earlier, as they are out of support now, even by me!). But last edition, we were getting extremely close to the 700 page limit I have had for most editions of the book. If you are a reader, or have ideas even from the list above about what needn’t be covered, please hit the comments. I am very interested to know. I have ideas about how to expand the book, but it will not be easy to see too much of an expansion, and it will be very hard to shrink what is there without a LOT of editing/rewriting, which is also not a beloved idea.

One idea I have is to remove the internals stuff about how indexes and data is stored, as other people do it better, and with In-Memory to contend with, it will really need to expand. It is however something that I find people feel like it is a selling point in that it provides a deep enough dive to give the reader a picture of internals, but not nearly as good as quite a few others do.

I look forward to comments from you as much as I hope you look forward to the months of upcoming blog posts with queries that show how each of these features work as I figure them out myself.

Note: fixed a few issues with formatting.. I hate HTML :).
Update: Added Hadoop Integration as a do not cover topic.  
Update: Added Delayed Durability. 
Update: Added link to Temporal Topic 
Update: Incremental Statistics
Update: Added link to In Memory OLTP article(s) 
Update: Added Tips and Tricks along with a link to first Tips and Tricks blog 
Update: Added Dynamic Data Masking link 
Updated: Added Row Level Security link